package com.siemens.industrial.web.mvc.dao.partner;

import com.siemens.industrial.model.partner.DeviceEventRelation;
import com.siemens.industrial.model.partner.Event;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Types;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by qupen on 2016/8/31.
 */
@Service
public class PartnerDeviceEventRelationDao {
    @Resource
    JdbcTemplate jdbcTemplate;

    private static final String databaseTag = "{database}";
    private static final String getDeviceEventRelationQuery = "SELECT * FROM `" + databaseTag + "`.`device_event_relation` WHERE `device_id` = ? AND `event_id` = ?";
    private static final String getDeviceEventRelationsQuery = "SELECT * FROM `" + databaseTag + "`.`device_event_relation`";
    private static final String getDeviceEventRelationsInDeviceQuery = "SELECT * FROM `" + databaseTag + "`.`device_event_relation` WHERE `device_id` = ?";
    private static final String getDeviceEventRelationsInEventQuery = "SELECT * FROM `" + databaseTag + "`.`device_event_relation` WHERE `event_id` = ?";
    private static final String saveDeviceEventRelationQuery = "INSERT INTO `" + databaseTag + "`.`device_event_relation` (`event_id`, `subscription`, `type`, `email`) VALUES (?, ?, ?, ?);";
    private static final String updateDeviceEventRelationQuery = "UPDATE `" + databaseTag + "`.`device_event_relation` SET `email` = ? WHERE `device_id` = ? AND `event_id` = ?";
    private static final String updateDeviceEventStateQuery = "UPDATE `" + databaseTag + "`.`device_event_relation` SET `state` = ? WHERE `device_id` = ? AND `event_id` = ?";
    private static final String deleteDeviceEventRelationQuery = "DELETE FROM `"+databaseTag+"`.`device_event_relation` WHERE `event_id`= ? and`subscription`= ? and`type`= ?;";
    private static final String deleteDeviceEventRelationByEventIdQuery = "DELETE FROM `" + databaseTag + "`.`device_event_relation` WHERE `event_id` = ?";
    private static final String selectDeviceSubQuery = "select* from (select * FROM `" + databaseTag + "`.device_event_relation where event_id = ?) a inner join `" + databaseTag + "`.device b on a.device_id =b.device_id;";
    private static final String deleteDeviceEventRelationByEventIdAndDviceIdQuery = "DELETE FROM `" + databaseTag + "`.`device_event_relation` WHERE `event_id` = ? AND device_id = ?";
    private static final String getOneByNameQuery = "select * from `" + databaseTag + "`.event where event_name =? limit 1";
    private static final String getAllMailByEventIdAndDeviceIdQuery = "SELECT group_concat(distinct  email) mails FROM `" + databaseTag + "`.device_event_relation where event_id=?;";
    private static final String insertMessage = "INSERT INTO `" + databaseTag + "`.`message` (`event_id`, `status`, `mtid`, `start_time`, `end_time`, `event_name`) VALUES (?, ?, ?, ?, ?, ?);";
    private static final String updateStatus = "UPDATE `" + databaseTag + "`.`message` SET `status`=? WHERE `event_id`=? and `mtid`=? and`start_time`=?;";
    private static final String findAllSubscriptionEventQuery = "SELECT * FROM `"+databaseTag+"`.device_event_relation where subscription = ? and type =?;";

    public DeviceEventRelation getDeviceEventRelation(String database, String deviceId, String eventId) {
        try {
            return jdbcTemplate.queryForObject(getDeviceEventRelationQuery.replace(databaseTag, database), new Object[]{deviceId, eventId}, new int[]{Types.VARCHAR, Types.VARCHAR}, DeviceEventRelation.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<DeviceEventRelation> getDeviceEventRelations(String database) {
        return jdbcTemplate.query(getDeviceEventRelationsQuery.replace(databaseTag, database), DeviceEventRelation.getRowMapper());
    }

    public List<DeviceEventRelation> getDeviceEventRelationsInDevice(String database, String deviceId) {
        return jdbcTemplate.query(getDeviceEventRelationsInDeviceQuery.replace(databaseTag, database), new Object[]{deviceId}, DeviceEventRelation.getRowMapper());
    }

    public List<DeviceEventRelation> getDeviceEventRelationsInEvent(String database, String eventId) {
        return jdbcTemplate.query(getDeviceEventRelationsInEventQuery.replace(databaseTag, database), new Object[]{eventId}, DeviceEventRelation.getRowMapper());
    }

    public void saveDeviceEventRelation(String database, DeviceEventRelation deviceEventRelation) {
        jdbcTemplate.update(saveDeviceEventRelationQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, deviceEventRelation.getEventId());
                    pss.setString(2, deviceEventRelation.getSubscription());
                    pss.setInt(3,deviceEventRelation.getType());
                    pss.setString(4, deviceEventRelation.getEmail());
                }
        );
    }

    public void deleteDeviceEventRelation(String database,String eventId,String subscription,int type) {
        jdbcTemplate.update(deleteDeviceEventRelationQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, eventId);
                    pss.setString(2, subscription);
                    pss.setInt(3,type);
                }
        );
    }

    public void deleteRelation(String database, String eventId) {
        jdbcTemplate.update(deleteDeviceEventRelationByEventIdQuery.replace(databaseTag, database), eventId);
    }

    public List<Map<String, Object>> getSubDevice(String database, String eventId) {
        return jdbcTemplate.queryForList(selectDeviceSubQuery.replace(databaseTag, database), eventId);
    }

    @Transactional
    public void deleteEventDeviceRelationByEventIdAndDeviceId(String database, String deviceId, String eventId) {
        jdbcTemplate.update(deleteDeviceEventRelationByEventIdAndDviceIdQuery.replace(databaseTag, database), eventId, deviceId);
    }

    public Event findOneByName(String database, String eventName) {
        try {
            return jdbcTemplate.queryForObject(getOneByNameQuery.replace(databaseTag, database), new Object[]{eventName}, Event.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public Map<String, Object> getAllMail(String database, String eventId) {
        return jdbcTemplate.queryForMap(getAllMailByEventIdAndDeviceIdQuery.replace(databaseTag, database), eventId);
    }

    public void insertMessage(String database, String eventId, int status, String mtid, Date startTime, Date endTime, String eventName) {
        jdbcTemplate.update(insertMessage.replace(databaseTag, database), eventId, status, mtid, startTime, endTime, eventName);
    }

    @Transactional
    public void updateMessageStatus(String database, String eventId, int status, String mtid, Date startTime) {
        jdbcTemplate.update(updateStatus.replace(databaseTag, database), status, eventId, mtid, startTime);
    }

    public List<Map<String,Object>> findAllSubscriptionByType(String database, String subscription,int type) {
       return jdbcTemplate.queryForList(findAllSubscriptionEventQuery.replace(databaseTag, database), subscription,type);
    }
}
